package xls;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;


import chucnang.ThuTien;

import trangchu.UtilDelegate;

import form.DSJ_XuatHang;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;



public class CongNo_TatCa {
	private static ThuTien thutien;
	public CongNo_TatCa(ThuTien thutien){
		this.thutien=thutien;
		exportToExcel();
	}
	public static void main(String[] args){
		
		javax.swing.SwingUtilities.invokeLater(new Runnable()
        {
            public void run()
            {
            	CongNo_TatCa a = new CongNo_TatCa(thutien);
            }
        });
	}
	public Vector<DSJ_XuatHang> getThuTien(){
		Vector<DSJ_XuatHang> getcongno = new Vector<DSJ_XuatHang>();
		Connection con =UtilDelegate.getConnection();		
		PreparedStatement st;
		try {
			st = con.prepareStatement("select * from KetThucGDBanHang");
			ResultSet rs = st.executeQuery();
			while(rs.next()){
				
				String chungTu= rs.getString("ChungTu");
				String iD_KhachHang = rs.getString("ID_KhachHang");
				String tenKH = rs.getString("TenKH");				
				String ngay = rs.getString("Ngay");
				String tongThanhToan = rs.getString("TongThanhToan");
				String daTra = rs.getString("DaTra");
				String conLai = rs.getString("ConLai");
				String ghiChu = rs.getString("GhiChu");
				
				DSJ_XuatHang b = new DSJ_XuatHang();
				b.setChungTu(chungTu);
				b.setID_KhachHang(iD_KhachHang);
				b.setTenKH(tenKH);
				b.setNgay(ngay);				
				b.setTongThanhToan(tongThanhToan);
				b.setDaTra(daTra);
				b.setConLai(conLai);
				b.setGhiChu(ghiChu);
				
				getcongno.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return getcongno;
	}
	public void exportToExcel(){		
		try {
			File f = new File("src/File_xls/danhsachCongNo.xls");
			File fOut = new File("src/FOut_xls/danhsachCongNo1.xls");
			Workbook inTemplate = Workbook.getWorkbook(f);
			WritableWorkbook out = Workbook.createWorkbook(fOut,inTemplate);
			WritableSheet sheet = out.getSheet(0);
			DateFormat dF = new SimpleDateFormat("dd/MM/yyyy");
			Label ld = new Label(2, 4, dF.format(new Date()));
			
			int PADDING_ROW = 6;
			int PADDING_COLUMN = 1;
			int i = 1;
			for(DSJ_XuatHang s:this.getThuTien()){
				Label cell0 = new Label(PADDING_COLUMN,PADDING_ROW+i,i+"");
				Label cell1 = new Label(PADDING_COLUMN+1,PADDING_ROW+i,s.getChungTu());
				Label cell2 = new Label(PADDING_COLUMN+2,PADDING_ROW+i,s.getID_KhachHang());
				Label cell3 = new Label(PADDING_COLUMN+3,PADDING_ROW+i,s.getTenKH());
				Label cell4 = new Label(PADDING_COLUMN+4,PADDING_ROW+i,s.getNgay());
				Label cell5 = new Label(PADDING_COLUMN+5,PADDING_ROW+i,s.getTongThanhToan());
				Label cell6 = new Label(PADDING_COLUMN+6,PADDING_ROW+i,s.getDaTra());
				Label cell7 = new Label(PADDING_COLUMN+7,PADDING_ROW+i,s.getConLai());
				Label cell8 = new Label(PADDING_COLUMN+8,PADDING_ROW+i,s.getGhiChu()+"");
				
				sheet.addCell(cell0);
				sheet.addCell(cell1);
				sheet.addCell(cell2);
				sheet.addCell(cell3);
				sheet.addCell(cell4);
				sheet.addCell(cell5);
				sheet.addCell(cell6);
				sheet.addCell(cell7);
				sheet.addCell(cell8);
				i++;
			}
			sheet.addCell(ld);
			out.write();
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}		
	}
}
